package com.o2o;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.PrintWriter;

public class guangxi {
    public static void main(String[] args) {

        int[] newyear ={1611936000,1614441600,1617033600,1619712000,1622304000};//2021
        int[] thisyear ={1580313600,1582819200,1585497600,1588176000,1590768000,1593446400,1596038400,1598716800,1601395200,1603987200,1606665600,1609257600};//2020
        int[] lastyear ={1548777600,1551283200,1553875200,1556553600,1559145600,1561824000,1564416000,1567094400,1569772800,1572364800,1575043200,1577635200};//2019

        for (int i = 0; i < 5; i++) {
            System.out.println("-------\n" +
                    "---" +(i+1)+"月--\n"+
                    "-------\n");
            System.out.println("select t1.city,t1.salesum/1000,t2.salesum/1000,t3.salesum/1000,t4.salesum/1000,t3.salesum/t4.salesum-1 from (\n" +
                    "-- 本月\n" +
                    "select city,sum(sellcount) sellsum,sum(salesamount) salesum \n" +
                    "from dbadmin.\"o2o_screen_ovresea_app_2021\" \n" +
                    "where province='广西壮族自治区' and city in ('南宁市','柳州市','北海市','钦州市','崇左市','防城港市')\n" +
                    "and timestamp =  \n" +newyear[i]+
                    "group by city) t1 left join (\n" +
                    "-- 去年同月\n" +
                    "select city,sum(sellcount) sellsum,sum(salesamount) salesum \n" +
                    "from dbadmin.\"o2o_screen_ovresea_app_2020\" \n" +
                    "where province='广西壮族自治区' and city in ('南宁市','柳州市','北海市','钦州市','崇左市','防城港市')\n" +
                    "and timestamp =  \n" +thisyear[i]+
                    "group by city)t2 on t1.city = t2.city left join (\n" +
                    "-- 累计\n" +
                    "select city,sum(sellcount) sellsum,sum(salesamount) salesum \n" +
                    "from dbadmin.\"o2o_screen_ovresea_app_2021\" \n" +
                    "where province='广西壮族自治区' and city in ('南宁市','柳州市','北海市','钦州市','崇左市','防城港市')\n" +
                    "and timestamp <=  \n" +newyear[i]+
                    "group by city)t3 on t1.city = t3.city left join (\n" +
                    "-- 同期累计\n" +
                    "select city,sum(sellcount) sellsum,sum(salesamount) salesum \n" +
                    "from dbadmin.\"o2o_screen_ovresea_app_2020\" \n" +
                    "where province='广西壮族自治区' and city in ('南宁市','柳州市','北海市','钦州市','崇左市','防城港市')\n" +
                    "and timestamp <=  \n" +thisyear[i]+
                    "group by city\n" +
                    ") t4 on t1.city = t4.city;\n");

        }
    }
}
